package novel.front.novellist.test;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlJsonConvertor {
    public static void main(String[] args) {
//        username: ai_container_db
//        password: ai_container_db
//        url: jdbc:mysql://mysql.test.baiwang-inner.com:3306/ai_container_db?useSSL=false&useUnicode=true&characterEncoding=utf-8
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://mysql.test.baiwang-inner.com:3306/ai_container_db";
        String user = "ai_container_db";
        String passwd = "ai_container_db";
        String sql = "select * from ai_scan_title";

        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url, user, passwd);
            Statement st =  con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int colnum = rsmd.getColumnCount();
            String val = "";
            String colName = "";
            JSONObject jobj = new JSONObject();
            JSONArray jArr = new JSONArray();

            while(rs.next()) {
                for(int i = 1; i<= colnum; i++) {
                    colName = rsmd.getColumnLabel(i);
                    if(1==i) {
                        val = new Integer(rs.getInt(colName)).toString();
                    }else {
                        val = rs.getString(colName);
                    }

                    try {
                        jobj.put(colName, val);

                    } catch (JSONException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                jArr.put(jobj);
            }
            System.out.println("Here is the json String:");
            System.out.println(jArr.toString());

        }catch(ClassNotFoundException e) {
            System.out.println("Driver not found");
            e.printStackTrace();
        }catch(SQLException e) {
            e.printStackTrace();
        }
    }
}
